package baseDados;

public class Queries {

	// Stock
	public static final String selectAllStocks = "select * from `mystockdb`.`stock`;";
	
	public static final String selectLastStocks = "select * from ( select * from `mystockdb`.`stock`, `mystockdb`.`empresa` WHERE stock.empresa_sigla = empresa.sigla ORDER BY data DESC) as t1 GROUP BY empresa_sigla;";
	
	public static final String insertStock = "insert into `mystockdb`.`stock` values (?, ?, ?, ?, ?, ?, ?);";

	public static final String deleteStock = "DELETE FROM `mystockdb`.`stock` WHERE data < ?;";
	
	public static final String[] insertSeveralStocks = {("INSERT INTO `mystockdb`.`stock` (`data`, `variacao`, `cotacao`, `valoraltodia`,`valorbaixodia`, `volume`, `empresa_sigla`) VALUES "), ("(?, ?, ?, ?, ?, ?, ?), "), ("(?, ?, ?, ?, ?, ?, ?);")};
	
	// Empresa
	public static final String selectAllEmpresas = "select * from `mystockdb`.`empresa`;";
	
	public static final String selectEmpresa = "select * from `mystockdb`.`empresa` where sigla = ?;";
	
	public static final String insertEmpresa = "insert into `mystockdb`.`empresa` values (?, ?);";
	
	public static final String[] insertSeveralEmpresas = {("INSERT INTO `mystockdb`.`empresa` (`sigla`, `nome`) VALUES "), ("(?, ?), "), ("(?, ?);")};
	
	
	// Utilizador
	public static final String selectAllUtilizadores = "select * from `mystockdb`.`utilizador`;";
	
	public static final String selectUtilizador = "select * from `mystockdb`.`utilizador` WHERE nome = ?;";
	
	public static final String updateUtilizador = "update `mystockdb`.`utilizador` set saldo = ?, pontos = ? where email = ?;";
	
	// Ac��o
	public static final String selectAllAccoesFromOneUtilizador = "select * from `mystockdb`.`accao` where utilizador_email = ?;";
	
	public static final String insertAccoesFromOneUtilizador = "insert into `mystockdb`.`accao` values (?, ?, ?, ?, ?);";
	
	public static final String updateAccoesFromOneUtilizador = "update `mystockdb`.`accao` set quantidade = ?, valoraquisicao = ? where utilizador_email = ? and nome = ?;";
	
	public static final String deleteAccoesFromOneUtilizador = "delete from `mystockdb`.`accao` where utilizador_email = ? and sigla = ?;";
	
	// Registo
	public static final String selectAllRegistosFromOneUtilizador = "select * from `mystockdb`.`registo` where utilizador_email = ?;";
	
	public static final String insertRegisto = "insert into `mystockdb`.`registo` values (?, ?, ?, ?, ?, ?);";
	
	// Sugestoes
	
	public static final String selectAllSugestoes = "select * from `mystockdb`.`sugestoes`;";
	
	public static final String selectSugestoesFromOnesUtilizador = "select * from `mystockdb`.`sugestoes` where utilizador_email = ?;";
	
	public static final String updateSugestoes = "update `mystockdb`.`sugestoes` set analista = ?, vigarista = ?, transaccoes = ? where utilizador_email = ?";
	
}